A database management system (DBMS) is a layer of software between a physical database (i.e., the files storing the data in the database) and the user. The DBMS manages requests for database action (such as queries or updates) from a user. Additionally, the DBMS permits centralized control of security and data integrity requirements.
In some situations, it is desirable to retrieve data in the state in which the data existed at a specific previous point in time. FIG. 1 is a flow diagram illustrating an existing procedure 100 for accessing “past data” from a database or other data storage mechanism. Initially, the procedure identifies a previous time that the data values should reflect (block 102). A flashback command is executed that changes the system time to the previous time (block 104). The procedure then executes one or more commands to retrieve data values associated with the previous time (block 106). Finally, the procedure executes a flashback disable command that changes the system time back to the current time (block 108).
The above procedure is effective at retrieving data values associated with a previous time. However, if the procedure is in the flashback mode (i.e., a flashback command has changed the system time to the previous time), then the procedure must exit (or disable) the flashback mode before retrieving data values associated with the current time or associated with a different previous time. This switching into and out of the flashback mode is time-consuming and may result in the repeated compilation of similar database queries.
Various database languages, such as SQL (Structured Query Language), support special-purpose constructs referred to herein as “cursors”. Prior to retrieving the results of a specific query statement, the DBMS may perform a significant amount of preliminary work for the statement, such as parsing, semantic analysis, and query plan generation. A cursor stores the results of much of this preliminary work. Consequently, when a query statement arrives, the DBMS first attempts to match the statement to statements for which cursors have already been created. If a match is found, the cursor is shared by the query statements, and the overhead work is avoided.
A “flashback cursor” is a particular type of cursor that is used to access past data. A flashback cursor is created in response to receipt of a “flashback query”. Unlike conventional queries, flashback queries specify a flashback time, and return data as it existed at the specified flashback time. One technique for handling flashback queries is described in patent application Ser. No. 09/676,305, filed Sep. 29, 2000, entitled SYSTEM AND METHOD FOR PROVIDING FINE-GRAINED TEMPORAL DATABASE ACCESS, by JONATHAN D. KLEIN, et al, the contents of which are incorporated herein by this reference.
Because flashback queries return data as of a particular flashback time, the flashback time must be taken into account during the preliminary work performed on the flashback query. For example, the execution plan of a flashback query should not attempt to use access structures, such as indexes, that did not exist at the flashback time of the query for which they are being made. Because the preliminary work is based on the specific flashback time, flashback cursors generally cannot be used by flashback queries other than the one for which they are created. The inability to share flashback cursors can result in significant inefficiencies.